# -*- coding: utf-8 -*-

from __future__ import with_statement
'''from sqlite3 import dbapi2 as sqlite3'''
import MySQLdb
from contextlib import closing
from flask import Flask, request, session, g, redirect, url_for, abort, \
     render_template, flash

# configuration
DATABASE = '/tmp/flaskr.db'
DEBUG = True
SECRET_KEY = 'development key'
USERNAME = 'admin'
PASSWORD = 'default'

# create our little application :)
app = Flask(__name__)
app.config.from_object(__name__)
app.config.from_envvar('FLASKR_SETTINGS', silent=True)


def connect_db():
    """Returns a new connection to the database."""
#    return sqlite3.connect(app.config['DATABASE'])
    return MySQLdb.connect (host = "localhost", user = "root", passwd = "", db = "bridgeit")


@app.before_request
def before_request():
    """Make sure we are connected to the database each request."""
    g.db = connect_db()


@app.after_request
def after_request(response):
    """Closes the database again at the end of the request."""
    g.db.close()
    return response


#@app.route('/')
#def show_entries():
#    cur = g.db.execute('select title, text from entries order by id desc')
#    entries = [dict(title=row[0], text=row[1]) for row in cur.fetchall()]
#    return render_template('show_entries.html', entries=entries)

@app.route('/view_profile')
def view_profile():
    cursor = g.db.cursor()
    cursor.execute('call sp_fetch_users(\'tamer\')')
    users = [dict(fname=row[0], 
                  lname=row[1],
                  dob=row[2],
                  country=row[3],
                  gender=row[4],
                  occupation=row[5],
                  email=row[6],
                  userid=row[7]) for row in cursor.fetchall()]
    cursor.close()
    cursor = g.db.cursor()
    cursor.execute('call sp_fetch_user_bridges(1)')
    userbridges = [dict(datetime=row2[0], 
                        text=row2[1],
                        fromcountry=row2[2],
                        tocountry=row2[3],
                        photourl=row2[4],
                        videourl=row2[5],
                        likes=row2[6],
                        dislikes=row2[7]) for row2 in cursor.fetchall()]
    cursor.close()
    cursor = g.db.cursor()
    cursor.execute('call sp_fetch_user_likedbridges(1)')
    userLikedbridges = [dict(datetime=row3[0], 
                        text=row3[1],
                        fromcountry=row3[2],
                        tocountry=row3[3],
                        photourl=row3[4],
                        videourl=row3[5],
                        likes=row3[6],
                        dislikes=row3[7]) for row3 in cursor.fetchall()]
#    users = [dict(fname="tamer", lname="sherif")]
    return render_template('view_profile.html', users=users,userLikedbridges=userLikedbridges,userbridges=userbridges)


@app.route('/', methods=['GET', 'POST'])
def login():
    error = None
    if request.method == 'POST':
        if request.form['username'] != app.config['USERNAME']:
            error = 'Invalid username'
        elif request.form['password'] != app.config['PASSWORD']:
            error = 'Invalid password'
        else:
            session['logged_in'] = True
            flash('You were logged in')
            return redirect(url_for('view_profile'))
    return render_template('login.html', error=error)


@app.route('/logout')
def logout():
    session.pop('logged_in', None)
    flash('You were logged out')
    return render_template('login.html')


@app.route('/edit_profile')
def edit_profile():
    cursor = g.db.cursor()
    cursor.execute('call sp_fetch_users(\'tamer\')')
    user = [dict(fname=row[0], 
                  lname=row[1],
                  dob=row[2],
                  country=row[3],
                  gender=row[4],
                  occupation=row[5],
                  email=row[6],
                  userid=row[7],
                  username=row[8],
                  password=row[9]) for row in cursor.fetchall()]
    return render_template('edit_profile.html', user=user)

@app.route('/update_profile', methods=['GET', 'POST'])
def update_profile():
    #insert into database
    firstname = str(request.form['firstname'])
    errorFN = None
    lastname= str(request.form['lastname'])
    errorLN = None
    month = str(request.form['month'])
    errorMonth = None
    day = str(request.form['day'])
    errorDay = None
    year = str(request.form['year'])
    errorYear = None
    gender = str(request.form['gender'])
    if gender=='Male':
            ge = 'M'
    elif gender=='Female':
                ge = 'F'    
    errorGender = None
    country = str(request.form['country'])
    errorCountry = None
    email =str(request.form['email'])
    errorEmail = None
    occupation = str(request.form['occupation'])
    username = str(request.form['username'])
    errorUsername = None
    password = str(request.form['password'])
    confirmpassword = str(request.form['confirmpassword'])
    errorPassword = None
    
    if request.method == 'POST':
        if firstname == '':          
            errorFN = 'missing firstname'
        if lastname == '':
            errorLN = 'missing lastname'
        if month == '':
            errorMonth = 'missing month'
        if day =='':
            errorDay = 'missing day'
        if year =='':
            errorYear = 'missing year'
        if gender == '':
            errorGender = 'missing gender'
        if country =='':
            errorCountry ='missing home country'
        if email =='' or '@' not in email:
            errorEmail ='not a valid email'
        if username=='':
            errorUsername = 'missing username'
        if password=='' or confirmpassword =='':
            errorPassword = 'please fill both the password and its confirmation fields'
        if password != confirmpassword:
            errorPassword = 'mismatching passwords'
        if errorFN==None and errorLN==None and errorMonth==None and errorDay==None and errorYear==None and errorGender==None and errorCountry==None and errorEmail==None:
            date = str(request.form['year'])+'-'+str(request.form['month'])+'-'+str(request.form['day'])     
            cursor = g.db.cursor()
            cursor.execute ('SELECT id FROM `country` WHERE name = \''+country+"\'")
            cti = [(row[0]) for row in cursor.fetchall()]
            ct = cti[0]
            print ct
            cursor.close()
            cursor = g.db.cursor()
            query = 'call update_user(1,\''+firstname+'\',\''+lastname+'\',\''+date+'\','+str(ct)+',\''+ge+'\',\''+occupation+'\',\''+email+'\',\''+password+'\')'
#            cursor.execute('call update_user(1,\''+firstname+'\',\''+lastname+'\',\''+date+'\','+str(ct)+',\''+ge+'\',\''+occupation+'\',\''+email+'\',\''+password+'\')')
            cursor.execute('call update_user(?,?,?,?,?,?,?,?,?)',(1,firstname,lastname,date,ct,ge,occupation,email,password))
            flash(query)
            print(query)
        returrn render_template('update_profile.html', firstname=firstname, errorFN=errorFN,
                           lastname=lastname, errorLN=errorLN, month=month, errorMonth=errorMonth,
                               day=day, errorDay=errorDay, year=year, errorYear=errorYear,
                               gender=gender, errorGender=errorGender, country=country, errorCountry=errorCountry,
                               email=email, errorEmail=errorEmail, username=username, errorUsername=errorUsername,
                               errorPassword=errorPassword)



if __name__ == '__main__':
    app.run()

